from mysql import MySql
from mysql import config
import json
import os

# https://blog.csdn.net/BabyFish13/article/details/76074708
# 用Python将mysql库表结构导成excel 源码 mysqlToExcel.py

mysqlEn = MySql(**config)
mysqlEn.isLog = False

schemaName = config['database'] #数据库名
tabName = "t_item_jump" #表名


selectSql = "select TABLE_NAME tabName from information_schema.TABLES where TABLE_SCHEMA='{schemaName}';".format(
    schemaName=schemaName) #从数据库 information_schema 中查找数据库的所有表

data = mysqlEn.execute(selectSql) #查出数据库中的所有表
#print("输出所有表:",data) #输出所有表

'''
selectSql = "SELECT  TABLE_SCHEMA userName, \
        table_name tabName, \
        ORDINAL_POSITION colNo, \
        COLUMN_NAME colName, \
        COLUMN_TYPE dataType, \
        IS_NULLABLE isNull, \
        COLUMN_COMMENT colComment \
    FROM information_schema. COLUMNS \
    WHERE TABLE_SCHEMA = '{schemaName}' \
    AND table_name = '{tabName}' \
    ".format(schemaName=schemaName, tabName=tabName) #查询表结构

data = mysqlEn.execute(selectSql) #查出数据库中的所有表
'''

#print("输出表结构:",data) #输出表结构
#data = mysqlEn.use_db("user")
#data=mysqlEn.selectAll("user","*"," 1")

def select_struct(tabName,schemaName):
    sql = "SHOW TABLES LIKE '{tabName}';".format(tabName=tabName) #查找库中是否存某个表
    data = mysqlEn.execute(sql) #执行sql
    if len(data)<=0:
        print("数据库",schemaName,"中没有",tabName)
        return None

    selectSql = "SELECT  TABLE_SCHEMA userName, \
        table_name tabName, \
        ORDINAL_POSITION colNo, \
        COLUMN_NAME colName, \
        COLUMN_TYPE dataType, \
        IS_NULLABLE isNull, \
        COLUMN_COMMENT colComment \
    FROM information_schema. COLUMNS \
    WHERE TABLE_SCHEMA = '{schemaName}' \
    AND table_name = '{tabName}' \
    ".format(schemaName=schemaName, tabName=tabName) #查询表结构的sql
    data = mysqlEn.execute(selectSql) #执行sql
    #print("data",type(data))
    fields = []
    for sub in data:        
        keyArr = str(sub[3]).split("_")
        key = ""
        for k in keyArr:
            if k:
                k=k.title() if key else k                    
                key = key + k
        fields.append(key)
    
    return fields 

def printClientStr(tabName,fields):
    strField = '"'+tabName+'":['
    for v in fields:
        strField+='"'+v+'",'
    strField = strField[:len(strField)-1]+"]"
    print(strField)
    return strField


def readJson(fileName):
    d = None
    with open(os.path.join(fileName)) as f:
        d = json.load(f)
    return d

oldJson = readJson("jsonData/client_config_list_old.json")
newJson = readJson("E:/H5Game/Client/resource/config/data/client_config_list.json")
newJson = readJson("jsonData/client_config_list_new.json")

sql = "select TABLE_NAME from INFORMATION_SCHEMA.TABLES whereTABLE_SCHEMA='{schemaName}' and TABLE_NAME='{tabName}';".format(schemaName=schemaName, tabName=tabName)
sql = "SHOW TABLES LIKE '{tabName}';".format(tabName=tabName)
print(sql)
data = mysqlEn.execute(sql)
print(data)

tab = "t_guild_fire_party_reward"
fields = select_struct(tab,schemaName)
printClientStr(tab,fields)


'''

for key in oldJson:
    if key.find("..")==-1:
        atts = oldJson[key]
        attrNews = newJson.get(key)        
        tabFileds = select_struct(key,schemaName)
        if attrNews and len(atts)==0 and tabFileds:            
            c1 = len(tabFileds)
            c2 = len(attrNews)
            if tabFileds and c1!=c2:
                print("表",key,"字段设置有误",c1,c2)
'''
